package com.singfan.common.db;

import com.singfan.common.util.BeanUtil;
import com.singfan.common.util.CheckUtil;
import com.singfan.common.SingFanException;
import com.singfan.common.db.config.Table;

import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

/**
 * 数据库访问泛型基类
 * Author: HanSongHe
 * Date: 15-7-12 下午1:51
 */
public abstract class DaoBaseGeneric<T> extends DaoBase {
    private static final String TABLE_NAME_PLACE_HOLDER = "@table@";

    protected String pkWhereSql;
    protected String hashSelectByPKSqlTemp;
    protected String hashDeleteByPKSqlTemp;
    protected String hashUpdateByPKSqlTemp;
    protected String hashInsertSqlTemp;
    protected String hashReplaceSqlTemp;

    protected Class<T> beanClassType;
    protected String tableNameBase;
    protected TreeMap<String, String> primaryKeyMap = new TreeMap<>();
    protected TreeMap<String, String> commonColumnMap = new TreeMap<>();

    public DaoBaseGeneric(){
        beanClassType = getBeanClass();
        tableNameBase = DbUtil.className2TableName(beanClassType);
        fillMap();
        initPKWhereSql();
        initDefaultSql();
    }

    /**
     * 根据hashKey，获取散表后的表名
     * @param hashKey hashKey
     * @return 散表后的表名
     */
    protected abstract String getTableName(Object hashKey);

    /**
     * 将一个Bean对象的数据插入到数据库中
     * @param hashKey 散库散表用的key
     * @param t Bean对象
     */
    public void insert(Object hashKey, T t){
        List<Object> params = new ArrayList<>();

        Set<String> primaryKeySet = primaryKeyMap.keySet();
        for(String fieldName : primaryKeySet){
            Object fieldValue = BeanUtil.getBeanFieldValue(t, fieldName);
            params.add(fieldValue);
        }

        Set<String> commonColumnSet = commonColumnMap.keySet();
        for(String fieldName : commonColumnSet){
            Object fieldValue = BeanUtil.getBeanFieldValue(t, fieldName);
            params.add(fieldValue);
        }

        String tableName = getTableName(hashKey);
        String insertSql = hashInsertSqlTemp.replaceFirst(TABLE_NAME_PLACE_HOLDER, tableName);

        execute(hashKey, insertSql, params);
    }

    /**
     * 将一个Bean对象的数据插入到数据库中，并返回自增长列的值
     * @param hashKey 散库散表用的key
     * @param t Bean对象
     * @return 自增长列的值
     */
    public int insertWithAutoIncrement(final Object hashKey, T t){
        final List<Object> params = new ArrayList<>();

        Set<String> primaryKeySet = primaryKeyMap.keySet();
        for(String fieldName : primaryKeySet){
            Object fieldValue = BeanUtil.getBeanFieldValue(t, fieldName);
            params.add(fieldValue);
        }

        Set<String> commonColumnSet = commonColumnMap.keySet();
        for(String fieldName : commonColumnSet){
            Object fieldValue = BeanUtil.getBeanFieldValue(t, fieldName);
            params.add(fieldValue);
        }

        String tableName = getTableName(hashKey);
        final String insertSql = hashInsertSqlTemp.replaceFirst(TABLE_NAME_PLACE_HOLDER, tableName);

        Object executeResult = executeWithResult(hashKey, insertSql, params, new IStatementProcessor() {
            @Override
            public Object afterExecute(Statement statement) {
                try {
                    int autoIncrementKey = -1;
                    ResultSet resultSet = statement.getGeneratedKeys();
                    if (resultSet.next()) {
                        autoIncrementKey = resultSet.getInt(1);
                    }
                    resultSet.close();
                    return autoIncrementKey;
                } catch (SQLException ex) {
                    throw new SingFanException(String.format("DaoBaseGeneric<%1$s>.insertWithAutoIncrement().afterExecute() error: %2$s hashKey(%3$s) sql(%4$s) params(%5$s) !", beanClassType.getName(), ex.getMessage(), hashKey, insertSql, DbUtil.getSqlParamInfo(params)), ex);
                }
            }
        });

        return Integer.parseInt(executeResult.toString());
    }


    /**
     * 将一个Bean对象的数据插入或更新到数据库中
     * @param hashKey 散库散表的key
     * @param t Bean对象
     */
    public void replace(Object hashKey, T t){
        List<Object> params = new ArrayList<Object>();

        Set<String> primaryKeySet = primaryKeyMap.keySet();
        for(String fieldName : primaryKeySet){
            Object fieldValue = BeanUtil.getBeanFieldValue(t, fieldName);
            params.add(fieldValue);
        }

        Set<String> commonColumnSet = commonColumnMap.keySet();
        for(String fieldName : commonColumnSet){
            Object fieldValue = BeanUtil.getBeanFieldValue(t, fieldName);
            params.add(fieldValue);
        }

        String tableName = getTableName(hashKey);
        String replaceSql = hashReplaceSqlTemp.replaceFirst(TABLE_NAME_PLACE_HOLDER, tableName);
        execute(hashKey, replaceSql, params);
    }

    /**
     * 根据主键删除一条数据
     * @param hashKey 散库散表用的key
     * @param pks 主键列表（按照主键名的自然顺序）
     */
    public void deleteByPK(Object hashKey, long... pks){
        if(pks != null && pks.length > 0){
            if(primaryKeyMap.size() == pks.length){
                List<Object> primaryKeyParams = getPrimaryKeyParams(pks);
                String tableName = getTableName(hashKey);
                String deleteByPKSql = hashDeleteByPKSqlTemp.replaceFirst(TABLE_NAME_PLACE_HOLDER, tableName);
                execute(hashKey, deleteByPKSql, primaryKeyParams);
            }
            else{
                throw new SingFanException(String.format("DaoBaseGeneric<%1$s>.deleteByPK() error: pks length is %2$d, but primaryKey count is %3$d, hashKey(%4$s) !", beanClassType.getName(), pks.length, primaryKeyMap.size(), hashKey));
            }
        }
        else{
            throw new SingFanException(String.format("DaoBaseGeneric<%1$s>.deleteByPK() error: pks is empty !", beanClassType.getName()));
        }
    }

    /**
     * 根据主键更新一条数据
     * @param hashKey 散库散表用的key
     * @param t Bean对象
     */
    public void updateByPK(Object hashKey, T t){
        List<Object> params = new ArrayList<>();

        Set<String> commonColumnSet = commonColumnMap.keySet();
        for(String fieldName : commonColumnSet){
            Object fieldValue = BeanUtil.getBeanFieldValue(t, fieldName);
            params.add(fieldValue);
        }

        Set<String> primaryKeySet = primaryKeyMap.keySet();
        for(String fieldName : primaryKeySet){
            Object fieldValue = BeanUtil.getBeanFieldValue(t, fieldName);
            params.add(fieldValue);
        }

        String tableName = getTableName(hashKey);
        String updateByPKSql = hashUpdateByPKSqlTemp.replaceFirst(TABLE_NAME_PLACE_HOLDER, tableName);
        execute(hashKey, updateByPKSql, params);
    }

    /**
     * 根据主键查询一条数据
     * @param hashKey 散库散表用的key
     * @param pks 主键列表（按照主键名的自然顺序）
     * @return 由Bean对象承载的数据
     */
    public T selectByPK(Object hashKey, long... pks){
        if(pks != null && pks.length > 0){
            if(primaryKeyMap.size() == pks.length){
                List<Object> primaryKeyParams = getPrimaryKeyParams(pks);
                String tableName = getTableName(hashKey);
                String selectByPKSql = hashSelectByPKSqlTemp.replaceFirst(TABLE_NAME_PLACE_HOLDER, tableName);
                return selectOne(hashKey, selectByPKSql, primaryKeyParams);
            }
            else{
                throw new SingFanException(String.format("DaoBaseGeneric<%1$s>.selectByPK() error: pks length is %2$d, but primaryKey count is %3$d, hashKey(%4$s) !", beanClassType.getName(), pks.length, primaryKeyMap.size(), hashKey));
            }
        }
        else{
            throw new SingFanException(String.format("DaoBaseGeneric<%1$s>.selectByPK() error: pks is empty, hashKey(%2$s) !", beanClassType.getName(), hashKey));
        }
    }

    /**
     * 查询数据库中的记录是否存在
     * @param hashKey 散库散表用的key
     * @param sql 查询的sql语句
     * @param paramList sql语句的占位符的值列表（没有则为null）
     * @return true:存在  false:不存在
     */
    protected boolean isExist(final Object hashKey, final String sql, final List<Object> paramList){
        Object obj = executeQuery(hashKey, sql, paramList, new IResultSetReader(){
            @Override
            public Object readObject(ResultSet resultSet){
                try {
                    boolean exist = false;
                    if(resultSet.next()){
                        exist = true;
                    }
                    return exist;
                }
                catch (Exception ex){
                    throw new SingFanException(String.format("DaoBaseGeneric<%1$s>.isExist().readObject() error: %2$s hashKey(%3$s) sql(%4$s) paramList(%5$s) !", beanClassType.getName(), ex.getMessage(), hashKey, sql, DbUtil.getSqlParamInfo(paramList)), ex);
                }
            }
        });

        return Boolean.valueOf(obj.toString());
    }

    /**
     * 查询数据库中的记录是否存在
     * @param hashKey 散库散表用的key
     * @param sql 查询的sql语句
     * @param params sql语句的占位符的值列表（没有则为null）
     * @return true:存在  false:不存在
     */
    protected boolean isExist(Object hashKey, String sql, Object... params){
        List<Object> paramList = DbUtil.params2paramList(params);

        return isExist(hashKey, sql, paramList);
    }

    /**
     * 查询数据库中的记录是否存在
     * @param hashKey 散库散表用的key
     * @param sql 查询的sql语句
     * @return true:存在  false:不存在
     */
    protected boolean isExist(Object hashKey, String sql){
        return isExist(hashKey, sql, new ArrayList<Object>());
    }

    /**
     * 查询一条数据，没有符合条件的数据则为null。
     * @param hashKey 散库散表用的key
     * @param sql 查询的sql语句
     * @param paramList sql语句的占位符的值列表（没有则为null）
     * @return 由Bean对象承载的数据
     */
    protected T selectOne(final Object hashKey, final String sql, final List<Object> paramList){
        Object obj = executeQuery(hashKey, sql, paramList, new IResultSetReader(){
            @Override
            public Object readObject(ResultSet resultSet){
                try {
                    T result = null;
                    if(resultSet.next()){
                        result = beanClassType.newInstance();
                        fillBean(result, resultSet);
                    }
                    return result;
                }
                catch (Exception ex){
                    throw new SingFanException(String.format("DaoBaseGeneric<%1$s>.selectOne().readObject() error: %2$s hashKey(%3$s) sql(%4$s) paramList(%5$s) !", beanClassType.getName(), ex.getMessage(), hashKey, sql, DbUtil.getSqlParamInfo(paramList)), ex);
                }
            }
        });

        return (T)obj;
    }

    /**
     * 查询一条数据，没有符合条件的数据则为null。
     * @param hashKey 散库散表用的key
     * @param sql 查询的sql语句
     * @param params sql语句的占位符的值列表（没有则为null）
     * @return 由Bean对象承载的数据
     */
    protected T selectOne(Object hashKey, String sql, Object... params){
        List<Object> paramList = DbUtil.params2paramList(params);

        return selectOne(hashKey, sql, paramList);
    }

    /**
     * 查询一条数据，没有符合条件的数据则为null。
     * @param hashKey 散库散表用的key
     * @param sql 查询的sql语句
     * @return 由Bean对象承载的数据
     */
    protected T selectOne(Object hashKey, String sql){
        return selectOne(hashKey, sql, new ArrayList<Object>());
    }

    /**
     * 查询数据列表（没有数据时为空列表，非null值）
     * @param hashKey 散库散表用的key
     * @param sql 查询的sql语句
     * @param paramList sql语句的占位符的值列表（没有则为null）
     * @return 由Bean对象承载的数据列表
     */
    protected List<T> selectList(final Object hashKey, final String sql, final List<Object> paramList){
        Object obj = executeQuery(hashKey, sql, paramList, new IResultSetReader(){
            @Override
            public Object readObject(ResultSet resultSet){
                try {
                    List<T> result = new ArrayList<T>();
                    while(resultSet.next()){
                        T t = beanClassType.newInstance();
                        fillBean(t, resultSet);
                        result.add(t);
                    }
                    return result;
                }
                catch (Exception ex){
                    throw new SingFanException(String.format("DaoBaseGeneric<%1$s>.selectList().readObject() error: %2$s hashKey(%3$s) sql(%4$s) paramList(%5$s) !", beanClassType.getName(), ex.getMessage(), hashKey, sql, DbUtil.getSqlParamInfo(paramList)), ex);
                }
            }
        });

        return (List<T>)obj;
    }

    /**
     * 查询数据列表（没有数据时为空列表，非null值）
     * @param hashKey 散库散表用的key
     * @param sql 查询的sql语句
     * @param params sql语句的占位符的值列表（没有则为null）
     * @return 由Bean对象承载的数据列表
     */
    protected List<T> selectList(Object hashKey, String sql, Object... params){
        List<Object> paramList = DbUtil.params2paramList(params);

        return selectList(hashKey, sql, paramList);
    }

    /**
     * 查询数据列表（没有数据时为空列表，非null值）
     * @param hashKey 散库散表用的key
     * @param sql 查询的sql语句
     * @return 由Bean对象承载的数据列表
     */
    protected List<T> selectList(Object hashKey, String sql){
        return selectList(hashKey, sql, new ArrayList<Object>());
    }

    /**
     * 根据hashKey.toString()的hashCode取模来散表（例：tableCount=3时，散列后的表名为 tableName_0, tableName_1, tableName_2）
     * @param hashKey 用作散表的key
     * @param version 当前散表的版本号
     * @return 散表后的表的名称
     */
    protected String getTableNameByHashCode(Object hashKey, int version){
        Table table = DbContext.getDbConfigProvider().getTable(tableNameBase);
        if (table != null){
            int hashId = Math.abs(hashKey.toString().hashCode());
            int tableCount = table.getTableCount(version);
            if (tableCount > 0){
                int tableIndex = hashId % tableCount;
                return tableNameBase + "_" + tableIndex;
            }
            else{
                throw new SingFanException(String.format("DaoBaseGeneric<%s>.getTableNameByHashCode() error: tableCount(%d) error in version(%d) !", tableNameBase, tableCount, version));
            }
        }
        else {
            throw new SingFanException(String.format("DaoBaseGeneric<%s>.getTableNameByHashCode() error: can not get table config !", tableNameBase));
        }
    }

    /**
     * 根据hashKey.toString()的尾数取模来散表（例：tableCount=3时，散列后的表名为 tableName_0, tableName_1, tableName_2）
     * @param hashKey 用作散表的key
     * @param version 当前散表的版本号
     * @return 散表后的表的名称
     */
    protected String getTableNameByMantissa(Object hashKey, int version){
        Table table = DbContext.getDbConfigProvider().getTable(tableNameBase);
        if (table != null){
            if (CheckUtil.isPositiveInteger(hashKey.toString())){
                long hashId = Long.parseLong(hashKey.toString());
                int tableCount = table.getTableCount(version);
                if (tableCount > 0){
                    long tableIndex = hashId % tableCount;
                    return tableNameBase + "_" + tableIndex;
                }
                else{
                    throw new SingFanException(String.format("DaoBaseGeneric<%s>.getTableNameByMantissa() error: tableCount(%d) error in version(%d) !", tableNameBase, tableCount, version));
                }
            }
            else {
                throw new SingFanException(String.format("DaoBaseGeneric<%s>.getTableNameByMantissa() error: hashKey(%s) is not long type !", tableNameBase, hashKey));
            }
        }
        else {
            throw new SingFanException(String.format("DaoBaseGeneric<%s>.getTableNameByMantissa() error: can not get table config !", tableNameBase));
        }
    }

    /**
     * 根据hashKey.toString()的hashCode取模来散表（例：tableCount=3时，散列后的表名为 tableName_0, tableName_1, tableName_2）
     * @param hashKey 用作散表的key
     * @param version 当前散表的版本号
     * @return 散表后的表的名称
     */
    protected String getDatabaseIdByHashCode(Object hashKey, int version){
        Table table = DbContext.getDbConfigProvider().getTable(tableNameBase);
        if (table != null){
            int hashId = Math.abs(hashKey.toString().hashCode());
            List<String> databaseIdList = table.getDatabaseIdList(version);
            if (databaseIdList != null && databaseIdList.size() > 0){
                int dbIndex = hashId % databaseIdList.size();
                return databaseIdList.get(dbIndex);
            }
            else{
                throw new SingFanException(String.format("DaoBaseGeneric<%s>.getDatabaseIdByHashCode() error: database config not found in version(%d) !", tableNameBase, version));
            }
        }
        else {
            throw new SingFanException(String.format("DaoBaseGeneric<%s>.getDatabaseIdByHashCode() error: can not get table config !", tableNameBase));
        }
    }

    /**
     * 根据hashKey.toString()的尾数取模来散库
     * @param hashKey 用作散表的key
     * @param version 当前散表的版本号
     * @return 散表后的表的名称
     */
    protected String getDatabaseIdByMantissa(Object hashKey, int version) {
        Table table = DbContext.getDbConfigProvider().getTable(tableNameBase);
        if (table != null){
            if (CheckUtil.isPositiveInteger(hashKey.toString())){
                long hashId = Long.parseLong(hashKey.toString());
                List<String> databaseIdList = table.getDatabaseIdList(version);
                if (databaseIdList != null && databaseIdList.size() > 0){
                    int dbIndex = (int)(hashId % databaseIdList.size());
                    return databaseIdList.get(dbIndex);
                }
                else{
                    throw new SingFanException(String.format("DaoBaseGeneric<%s>.getDatabaseIdByMantissa() error: database config not found in version(%d) !", tableNameBase, version));
                }
            }
            else {
                throw new SingFanException(String.format("DaoBaseGeneric<%s>.getDatabaseIdByMantissa() error: hashKey(%s) is not long type !", tableNameBase, hashKey));
            }
        }
        else {
            throw new SingFanException(String.format("DaoBaseGeneric<%s>.getDatabaseIdByMantissa() error: can not get table config !", tableNameBase));
        }
    }

    /**
     * 将ResultSet中的数据填充到Bean对象中
     * @param object Bean对象
     * @param resultSet ResultSet对象
     * @throws SQLException
     */
    protected void fillBean(Object object, ResultSet resultSet) throws SQLException {
        DbUtil.fillBean(object, resultSet);
    }

    /**
     * 获取数据库表最新的版本号
     * @return 数据库表最新的版本号
     */
    protected int getTableNewestVersion(){
        return DbContext.getDbConfigProvider().getTable(tableNameBase).getNewestVersion();
    }

    private Class<T> getBeanClass(){
        ParameterizedType parameterizedType = (ParameterizedType)this.getClass().getGenericSuperclass();
        return (Class<T>)(parameterizedType.getActualTypeArguments()[0]);
    }

    private void fillMap(){
        List<Field> fileList = BeanUtil.getBeanFieldList(beanClassType);
        for(Field field : fileList){
            String fieldName = field.getName();
            if(DbUtil.isPrimaryKeyFiled(field)){
                primaryKeyMap.put(fieldName, DbUtil.fieldName2ColumnName(field));
            }
            else if(!DbUtil.isIgnoreFiled(field)){
                commonColumnMap.put(fieldName, DbUtil.fieldName2ColumnName(field));
            }
        }
    }

    private void initPKWhereSql(){
        pkWhereSql = "WHERE ";
        Collection<String> primaryKeys = primaryKeyMap.values();
        int index = 0;

        for(String primaryKey : primaryKeys){
            if(index == 0){
                pkWhereSql += primaryKey + "=?";
            }
            else{
                pkWhereSql += " AND " + primaryKey + "=?";
            }
            index++;
        }
    }

    private void initDefaultSql(){
        String insertColumns = "(";
        String insertValues = "(";
        String updateValues = "";

        Set<String> primaryFields = primaryKeyMap.keySet();
        int pIndex = 0;
        for(String primaryField : primaryFields){
            if(pIndex == 0){
                insertColumns += primaryKeyMap.get(primaryField);
                insertValues += "?";
            }
            else{
                insertColumns += "," + primaryKeyMap.get(primaryField);
                insertValues += ",?";
            }
            pIndex++;
        }

        Set<String> commonFields = commonColumnMap.keySet();
        int cIndex = 0;
        for(String commonField : commonFields){
            String commonColumn = commonColumnMap.get(commonField);
            insertColumns += "," + commonColumnMap.get(commonField);
            insertValues += ",?";
            if(cIndex == 0){
                updateValues += commonColumn + "=?";
            }
            else{
                updateValues += "," + commonColumn + "=?";
            }
            cIndex++;
        }

        insertColumns += ")";
        insertValues += ")";

        hashSelectByPKSqlTemp = String.format("SELECT * FROM %1$s %2$s", TABLE_NAME_PLACE_HOLDER, pkWhereSql);
        hashDeleteByPKSqlTemp = String.format("DELETE FROM %1$s %2$s", TABLE_NAME_PLACE_HOLDER, pkWhereSql);
        hashUpdateByPKSqlTemp = String.format("UPDATE %1$s SET %2$s %3$s", TABLE_NAME_PLACE_HOLDER, updateValues, pkWhereSql);
        hashInsertSqlTemp = String.format("INSERT INTO %1$s %2$s VALUES %3$s", TABLE_NAME_PLACE_HOLDER, insertColumns, insertValues);
        hashReplaceSqlTemp = String.format("REPLACE INTO %1$s %2$s VALUES %3$s", TABLE_NAME_PLACE_HOLDER, insertColumns, insertValues);
    }

    private static List<Object> getPrimaryKeyParams(long[] ids){
        List<Object> primaryKeyList = new ArrayList<>();
        for(long id : ids){
            primaryKeyList.add(id);
        }
        return primaryKeyList;
    }
}
